package com.aternity.geolocations.firebird;

import au.com.bytecode.opencsv.CSVWriter;
import com.aternity.common.geolocation.PolygonFormatter;
import com.aternity.common.geom.Polygon2D;
import java.awt.geom.Rectangle2D;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

/**
 * @author ddd
 * @since 11/27/13 2:30 PM
 */
public class FirebirdExtractPolygons {

    Connection connection;
    JdbcTemplate jdbcTemplate;

    PolygonFormatter polygonFormatter = new PolygonFormatter();
    RectangleFormatter rectangleFormatter = new RectangleFormatter();
    TableauNameCache nameCache;

    public FirebirdExtractPolygons() throws Exception {
        Class.forName("org.firebirdsql.jdbc.FBDriver");
        Properties props = new Properties();
        props.setProperty("user", "SYSDBA");
        props.setProperty("password", "masterkey");
        props.setProperty("encoding", "UNICODE_FSS");
        connection = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:C:/dev/geoloc/GEOCODING.fdb", props);
        jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(connection, true));
        nameCache = new TableauNameCache(new File("tableau.entity.names.csv"));
    }

    void extractCountryPolygons() throws Exception {
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("country.geom.csv"), "UTF-8"))) {
            final PreparedStatement ps = connection.prepareStatement(SELECT_COUNTRIES_POLYGONS);
            final ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                int countryId = rs.getInt(1);
                String iso3166_3 = rs.getString(2);
                String geom = rs.getString(3);
                String countryName = nameCache.getEntityName(countryId);
                //System.out.println(countryId + ", " + countryName + ", " + iso3166_3 + ", " + geom);
                String boundariesStr = geom == null ? "" : extractBoundaries(polygonFormatter, rectangleFormatter, geom);
                if (geom == null) {
                    geom = "";
                }
                csvWriter.writeNext(new String[]{Integer.toString(countryId), countryName, iso3166_3, geom, boundariesStr});
            }
        }
    }

    void extractStatePolygons() throws Exception {
        try (CSVWriter csvWriter = new CSVWriter(new OutputStreamWriter(new FileOutputStream("state.geom.csv"), "UTF-8"))) {
            final List<StateInfo> stateInfos = jdbcTemplate.query(SELECT_STATE_POLYGONS, new StateRowMapper());
            for (StateInfo stateInfo : stateInfos) {
                writeStateDataCsvLine(csvWriter, stateInfo);
            }
        }
    }

    private static String extractBoundaries(PolygonFormatter polygonFormatter, RectangleFormatter rectangleFormatter, String geom) {
        List<Polygon2D> countryPolygons = polygonFormatter.parse(geom);
        List<Rectangle2D> boundaries = new ArrayList<>();
        for (Polygon2D polygon : countryPolygons) {
            boundaries.add(polygon.getBounds2D());
        }
        return rectangleFormatter.formatMultiRectangles(boundaries);
    }

    private void writeStateDataCsvLine(CSVWriter csvWriter, StateInfo ri) {
        String boundariesStr = ri.geom == null ? "" : extractBoundaries(polygonFormatter, rectangleFormatter, ri.geom);
        csvWriter.writeNext(new String[]{Integer.toString(
            ri.countryId), ri.countryName, ri.iso3166_3, Integer.toString(ri.stateId), ri.stateName, ri.geom, boundariesStr});
    }

    private class StateRowMapper implements RowMapper<StateInfo> {
        @Override
        public StateInfo mapRow(ResultSet rs, int i) throws SQLException {
            StateInfo ri = new StateInfo();
            ri.countryId = rs.getInt(1);
            ri.iso3166_3 = rs.getString(2);
            ri.countryName = nameCache.getEntityName(ri.countryId);
            ri.stateId = rs.getInt(3);
            ri.stateName = nameCache.getEntityName(ri.stateId);
            ri.geom = rs.getString(4);
            return ri;
        }
    }

    private static class StateInfo {
        int countryId;
        String iso3166_3;
        String countryName;
        int stateId;
        String stateName;
        String geom;
    }

    public static void main(String[] args) throws Exception {
        FirebirdExtractPolygons extractPolygons = new FirebirdExtractPolygons();
        extractPolygons.extractCountryPolygons();
        extractPolygons.extractStatePolygons();
    }

    private static final String SELECT_COUNTRIES_POLYGONS
            = "select \n"
            + "    country.ID, country.ISO3166_2, ldc.\"Geometry\" as geom\n"
            + "from \n"
            + "    \"Country\" country \n"
            + "    join \"LocalDataCountry\" ldc on ldc.\"ParentID\"=country.ID\n"
            + "group by\n"
            + "    country.ID, country.ISO3166_2, geom";

    private static final String SELECT_STATE_POLYGONS
            = "select \n"
            + "        country.ID as \"countryId\", \n"
            + "        country.ISO3166_2, \n"
            + "        state.ID as \"stateId\" ,\n"
            + "        lds.\"Geometry\" as geom\n"
            + "from \n"
            + "    \"State\" state\n"
            + "    join \"Country\" country on state.\"ParentID\"=country.ID\n"
            + "    join \"LocalDataState\" lds on lds.\"ParentID\"=state.ID";

}
